package com.unittec.drainage.provider.auth.excel.imports;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.unittec.drainage.provider.ApplicationContextProvider;
import com.unittec.drainage.provider.auth.entity.SysUser;
import com.unittec.drainage.provider.auth.mapper.ImportTaskMapper;
import com.unittec.drainage.provider.auth.service.BaseService;
import com.unittec.drainage.provider.auth.service.SysUserService;
import com.unittec.drainage.provider.common.IConstants;
import com.unittec.drainage.provider.entity.ImportTaskEntity;
import com.unittec.drainage.provider.excel.annotation.Excel;
import com.unittec.drainage.provider.excel.annotation.ExcelCell;
import com.unittec.drainage.provider.excel.utils.ExcelRow;
import com.unittec.drainage.provider.excel.utils.ExcelUtil;
import com.unittec.drainage.provider.excel.utils.IExcelImportConsumer;
import com.unittec.drainage.provider.excel.utils.IExcelTemplateOfImportConsumer;
import com.unittec.drainage.provider.log.Log;
import com.unittec.drainage.provider.scope.ThreadLocalScope;
import com.unittec.drainage.provider.utils.*;
import com.unittec.drainage.sdk.auth.domain.ImportTaskDto;
import com.unittec.drainage.sdk.auth.request.Req101921;
import com.unittec.drainage.sdk.auth.request.Req101923;
import com.unittec.drainage.sdk.root.common.CommonConstant;
import com.unittec.drainage.sdk.root.domain.GatewayRequest;
import com.unittec.drainage.sdk.root.internal.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
import org.apache.http.client.fluent.Request;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Service;
import org.springframework.util.FileCopyUtils;
import org.springframework.util.ReflectionUtils;

import javax.annotation.Resource;
import java.io.*;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.text.ParseException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.stream.Collectors;

;

/**
 * @Description:导入service
 * @Author: wuxinwei
 * @CreateDate: 2019年12月2日14:08:18
 */
@Service
public class ExcelImportService {

    @Resource
    private Log log;

    @Resource
    private ImportTaskService importTaskService;

    @Resource
    private BaseService baseService;

    @Resource
    private MqExcelImportProducerService producerService;

    @Resource
    private ImportTaskMapper importTaskMapper;

    @Resource
    private SysUserService sysUserService;

    /**
     *
     */
    private static final int DAY = 7;
    /**
     *
     */
    private static final String EXCEL_IMPORT_MAPPING = "excel_import_mapping";


    private static final Map<String, Map<ExcelCell, Field>> typeClassFieldCache = new ConcurrentHashMap<>();
    private static final Map<String, Map<ExcelCell, Map<String, String>>> typeClassFieldFormatCache =
            new ConcurrentHashMap<>();

    private final Object locker = new Object();


    public <T extends ExcelRow> Map<String, List<T>> importExcel(String consumer, String key, String path, String fileName,
                                                                 String type, boolean async, Map<String, Object> paramMap) throws IOException, ClassNotFoundException {
        paramMap.put("consumer", consumer);
        paramMap.put("path", path);
        paramMap.put("fileName", fileName);
        paramMap.put("type", type);

        // 获取数据字典映射信息
        String typeClassStr = baseService.getDicMapping(EXCEL_IMPORT_MAPPING, type, paramMap);

        File file = File.createTempFile(UUID.randomUUID().toString(), null);

        // OssUtils.download(path, file);
        if (!Files.exists(file.getParentFile().toPath())) {
            Files.createDirectories(file.getParentFile().toPath());
        }
        byte[] bytes = Request.Get(path).connectTimeout(20000).execute().returnContent().asBytes();
        FileCopyUtils.copy(bytes, file);
        //
        consumer += IConstants.ExcelImportConsumer;

        consumer = Character.toLowerCase(consumer.charAt(0)) + consumer.substring(1);
        @SuppressWarnings("unchecked")
        IExcelImportConsumer<T> excelImportConsumer =
                (IExcelImportConsumer<T>) ApplicationContextProvider.getBean(consumer);

        Class typeClass = Class.forName(typeClassStr);
        Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
        if (excel == null) {
            throw new BusinessException(typeClass.getName() + "找不到Excel注解");
        }
        int totalRow;
        try (InputStream is = new FileInputStream(file)) {
            totalRow = ExcelUtil.getTotalRow(is, typeClass);
        } catch (Exception e) {
            throw new BusinessException(typeClass.getName() + "获取总条数失败");
        }
        ImportTaskEntity importTask = new ImportTaskEntity();
        String importTaskId = UUIDUtils.getId();
        importTask.setId(importTaskId);
        importTask.setTotal(totalRow);
        importTask.setStatus(CommonConstant.FILE_STATUS_0);
        importTask.setFilePath(key);
        importTask.setConsumer(consumer);
        importTask.setType(type);
        importTask.setOriginalFileName(fileName);
        importTaskService.save(importTask);

        paramMap.put("importTaskId", importTask.getId());

        // 默认异步后台导入
        // 同步导入
        if (async) {
            return syncImportData(consumer, fileName, paramMap, excelImportConsumer, file, importTask);
        } else {
            asynImportData(consumer, fileName, key, path, type, paramMap, importTaskId);
            if (file != null) {
                file.delete();
            }
            return null;
        }
    }

    /**
     * 异步导入
     *
     * @param consumer
     * @param fileName
     * @param filePath
     * @param type
     * @param paramMap
     * @param importTaskId
     */
    private void asynImportData(String consumer, String fileName, String fileKey, String filePath, String type,
                                Map<String, Object> paramMap, String importTaskId) {
        GatewayRequest<Req101921> getway = new GatewayRequest<>();
        Req101921 req = new Req101921();
        req.setConsumer(consumer);
        req.setFileName(fileName);
        req.setFilePath(filePath);
        req.setFileKey(fileKey);
        req.setAsync(false);
        req.setType(type);
        req.setParamMap(paramMap);
        req.setImportTaskId(importTaskId);
        req.setAppId(ThreadLocalScope.getAppId());
        req.setIp(ThreadLocalScope.getAuthData().getIp());
        req.setAppCode(ThreadLocalScope.getAuthData().getAppCode());
        req.setRequestId(ThreadLocalScope.getAuthData().getRequestId());
        req.setTenantId(ThreadLocalScope.getAuthData().getTenantId());
        req.setToken(ThreadLocalScope.getAuthData().getToken());
        req.setUser(ThreadLocalScope.getAuthData().getUser());
        getway.setContent(req);
        producerService.createExcelImportMsg(getway);
    }

    /**
     * 同步导入数据
     *
     * @param consumer
     * @param fileName
     * @param paramMap
     * @param excelImportConsumer
     * @param file
     * @param importTask
     * @param <T>
     * @return
     */
    private <T extends ExcelRow> Map<String, List<T>> syncImportData(String consumer, String fileName,
                                                                     Map<String, Object> paramMap, IExcelImportConsumer<T> excelImportConsumer, File file,
                                                                     ImportTaskEntity importTask) {
        try {
            File resultFile = File.createTempFile(UUID.randomUUID().toString(), null);
            List<? extends ExcelRow> dataList;
            try (FileInputStream is1 = new FileInputStream(file)) {

                dataList = importExcelSync(is1, excelImportConsumer, importTask, paramMap, resultFile);

                List<T> errorList =
                        (List<T>) dataList.stream().filter(e -> e.getErrorMessage() != null).collect(Collectors.toList());
                List<T> successList =
                        (List<T>) dataList.stream().filter(e -> e.getErrorMessage() == null).collect(Collectors.toList());

                // 文件上传到阿里云文件服务器
                int index = fileName.lastIndexOf(".");
                String resultFileName =
                        (index == -1 ? fileName + "_result" : fileName.substring(0, index + 1) + "_result");

                // 文件后缀
                String fileTyle = fileName.substring(fileName.lastIndexOf("."), fileName.length());

                String resultFilePath = OssUtils.upload(resultFile,
                        IConstants.EXCEL_IMPORT + "/" + consumer.replace(IConstants.ExcelImportConsumer, ""),
                        resultFileName + "." + fileTyle);
                importTask.setSuccessful(successList.size());
                importTask.setStatus(CommonConstant.FILE_STATUS_2);
                importTask.setResultFilePath(resultFilePath);
                importTaskService.updateById(importTask);

                Map<String, List<T>> result = new HashMap<>(16);
                result.put("successList", successList);
                result.put("errorList", errorList);
                List resultUrlList = new ArrayList<>();
                String resultUrl = OssUtils.getUrl(resultFilePath, DAY);
                resultUrlList.add(resultUrl);
                result.put("resultUrl", resultUrlList);
                return result;
            } finally {
                resultFile.delete();
            }
        } catch (Exception e) {
            log.sysErrLog("importExcel", e.getMessage(), e);
            importTask.setStatus(CommonConstant.FILE_STATUS_3);
            importTask.setRemark(e.getMessage());
            importTaskService.updateById(importTask);
            throw new BusinessException(e);
        } finally {
            if (file != null) {
                file.delete();
            }
        }
    }

    /**
     * 同步导入
     *
     * @param inputStream
     * @param excelImportConsumer
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     */
    public <T extends ExcelRow> List<T> importExcelSync(InputStream inputStream,
                                                        IExcelImportConsumer<T> excelImportConsumer, ImportTaskEntity importTask, Map<String, Object> paramMap,
                                                        File resultFile) throws ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("文件格式错误");
        }
        try {
            List<T> dataList = parseExcelSync(workbook, excelImportConsumer, importTask, paramMap);
            return dataList;
        } catch (Exception e) {
            e.printStackTrace();
            throw new BusinessException("解析数据错误");
        } finally {
            try (FileOutputStream fos = new FileOutputStream(resultFile)) {
                workbook.write(fos);
                fos.flush();
                fos.close();
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 同步导入数据
     */
    private void asyncImportData() {

    }

    public <T extends ExcelRow> void importExcelAsync(String importTaskId, String consumer, String path,
                                                      String fileName, String type, Map<String, Object> paramMap) {
        paramMap.put("consumer", consumer);
        paramMap.put("path", path);
        paramMap.put("fileName", fileName);
        paramMap.put("type", type);

        // 查询数据库里面的导入任务数据
        ImportTaskEntity importTask = importTaskService.getById(importTaskId);
        File tempFile = null;
        File resultFile = null;
        try {
            tempFile = File.createTempFile(UUID.randomUUID().toString(), null);
            OssUtils.download(importTask.getFilePath(), tempFile);

            @SuppressWarnings("unchecked")
            IExcelImportConsumer<T> excelImportConsumer =
                    (IExcelImportConsumer<T>) ApplicationContextProvider.getBean(importTask.getConsumer());
            if (excelImportConsumer == null) {
                throw new BusinessException("找不到consumer " + importTask.getConsumer());
            }
            int successful =0;
            InputStream is = new FileInputStream(tempFile);
            resultFile = File.createTempFile(UUID.randomUUID().toString(), null);
            try {
                successful = importExcelAsync(is, excelImportConsumer, importTask, paramMap, resultFile);
                importTask.setSuccessful(successful);
                importTask.setStatus(CommonConstant.FILE_STATUS_2);
            } catch (Exception e) {
                log.sysErrLog("importExcelAsync", e.getMessage(), e);
                importTask.setStatus(CommonConstant.FILE_STATUS_3);
                importTask.setRemark(e.getMessage());
            }
            int index = importTask.getFilePath().lastIndexOf(".");
            String suffix = (index == -1 ? "" : "." + importTask.getFilePath().substring(index + 1));
            String resultFileName =
                    importTask.getFilePath().substring(importTask.getFilePath().lastIndexOf("/") + 1).replace(suffix, "")
                            + "_result" + suffix;
            // 文件上传到阿里云文件服务器
            String resultFilePath = OssUtils.upload(resultFile,
                    IConstants.EXCEL_IMPORT + "/" + importTask.getConsumer().replace(IConstants.ExcelImportConsumer, ""),
                    resultFileName);
            importTask.setResultFilePath(resultFilePath);
            importTask.setSuccessful(successful);
            importTaskService.updateById(importTask);
        } catch (Exception e) {
            importTask.setStatus(CommonConstant.FILE_STATUS_3);
            importTask.setRemark(e.getMessage());
            importTaskService.updateById(importTask);
        } finally {
            if (tempFile != null) {
                tempFile.delete();
            }
            if (resultFile != null) {
                resultFile.delete();
            }
        }
    }

    /**
     * @param consumer
     * @param type
     * @param <T>
     * @return
     */
    public <T extends ExcelRow> String getExcelTemplateOfImport(String consumer, String type) {
        // 获取数据字典映射信息
        String typeClassStr = baseService.getDicMapping(EXCEL_IMPORT_MAPPING, type);
        try {
            Class<T> typeClass = (Class<T>) Class.forName(typeClassStr);
            Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
            if (excel == null) {
                throw new BusinessException(typeClass.getName() + "找不到Excel注解");
            }
            //
            consumer += IConstants.EXCEL_TEMPLATE_OF_IMPORT_CONSUMER;
            consumer = Character.toLowerCase(consumer.charAt(0)) + consumer.substring(1);
            @SuppressWarnings("unchecked")
            IExcelTemplateOfImportConsumer<T> excelImportConsumer =
                    (IExcelTemplateOfImportConsumer<T>) ApplicationContextProvider.getBean(consumer);
            return excelImportConsumer.process(typeClass);
        } catch (ClassNotFoundException e) {
            throw new BusinessException("下载模板报错" + e.getLocalizedMessage());
        }
    }

    /**
     * 解析文件执行同步导入
     *
     * @param workbook
     * @param excelImportConsumer
     * @return
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public <T extends ExcelRow> List<T> parseExcelSync(Workbook workbook, IExcelImportConsumer<T> excelImportConsumer,
                                                       ImportTaskEntity importTask, Map<String, Object> paramMap)
            throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException {

        String typeClassStr = String.valueOf(paramMap.get("typeClass"));
        Class typeClass = Class.forName(typeClassStr);
        int sheetIndex = 0;

        Sheet sheet = workbook.getSheetAt(sheetIndex);

        Map<String, Integer> titleMap = ExcelUtil.getTitleMap(sheet, typeClass);
        int lastCellNum = 0;
        for (Map.Entry<String, Integer> entry : titleMap.entrySet()) {
            if (entry.getValue() != null) {
                if (entry.getValue() > lastCellNum) {
                    lastCellNum = entry.getValue();
                }
            }
        }

        String templateErrorMessage = "";
        try {
            ExcelUtil.checkTemplate(titleMap, typeClass);
        } catch (Exception e) {
            templateErrorMessage = "EXCEL模板错误";
        }
        Map<ExcelCell, Field> propertyMap = getFieldMap(typeClass);
        Map<ExcelCell, Map<String, String>> propertyFormatMap = ExcelUtil.getFieldFormatMap(typeClass);

        List<T> dataList = new ArrayList<>();
        List<T> successList = new ArrayList<>();
        Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
        int lastRowNum = sheet.getLastRowNum();
        int beginIndex = excel.firstDataRow();
        if (importTask.getLastRowIndex() != null && importTask.getLastRowIndex() > 0) {
            beginIndex = importTask.getLastRowIndex();
        }
        for (int i = beginIndex; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            if (ExcelUtil.isRowEmpty(row)) {
                continue;
            }
            T t = (T) ExcelUtil.parseRow(row, typeClass, propertyMap, propertyFormatMap, titleMap);
            try {
                parseRowOfEnum(t, propertyMap);
            } catch (Exception e) {
                t.setErrorMessage(e.getMessage());
            }
            if (!StringUtils.isBlank(templateErrorMessage)) {
                t.setErrorMessage(templateErrorMessage);
            }
            dataList.add(t);
            if (t.getErrorMessage() != null) {
                Cell cell = row.createCell(lastCellNum + 1, CellType.STRING);
                cell.setCellValue(t.getErrorMessage());
            } else {
                successList.add(t);
            }
        }
        if (!dataList.isEmpty()) {
            successList = dataList.stream().filter(e -> e.getErrorMessage() == null).collect(Collectors.toList());
            excelImportConsumer.consume(successList, importTask, paramMap);
            ExcelUtil.writeErrorMessage(sheet, dataList, titleMap, workbook);
        }
        return dataList;
    }

    /**
     * @param t
     * @param propertyMap
     * @param <T>
     */
    private <T extends ExcelRow> void parseRowOfEnum(T t, Map<ExcelCell, Field> propertyMap) {

        for (Map.Entry<ExcelCell, Field> entry : propertyMap.entrySet()) {
            ExcelCell excelCell = entry.getKey();
            Field field = entry.getValue();
            //
            String dropDownListName = excelCell.dropDownListName();
            if (StringUtil.isEmpty(dropDownListName)) {
                continue;
            }
            if (!excelCell.dropDownListDataSourceIsDic()) {
                continue;
            }
            Object field1 = ReflectionUtils.getField(field, t);
            if (field1 == null) {
                continue;
            }
            String value = baseService.getDicMapping(dropDownListName, field1.toString());
            if(StringUtils.isBlank(value)){
                throw  new BusinessException(excelCell.title()+" 不存在");
            }
            ReflectionUtils.setField(field, t, value);
        }
    }

    /**
     * 查询导入任务列表
     *
     * @param req101923
     * @return
     */
    public IPage<ImportTaskDto> importTaskList(Req101923 req101923) {
        String currentUser = ThreadLocalScope.getAuthData().getUser();
        IPage<ImportTaskEntity> result = importTaskMapper.selectPage(new Page<ImportTaskEntity>(req101923.getCurrent(), req101923.getSize()), new QueryWrapper<ImportTaskEntity>()
                .lambda()
                .eq(ImportTaskEntity::getCreateUser, currentUser)
                .orderByDesc(ImportTaskEntity::getCreateTime));


        SysUser sysUser = sysUserService.getById(currentUser);

        String userName="";
        if(sysUser!=null){
            userName=sysUser.getRealname();
        }else{
            userName=ThreadLocalScope.getAuthData().getUserName();
        }
        List<ImportTaskDto> list = new ArrayList<>();

        Page<ImportTaskDto> resultImportTaskDto = new Page<>(req101923.getCurrent(), req101923.getSize());

        if (result.getRecords() != null && result.getRecords().size() > 0) {

            for (ImportTaskEntity entity : result.getRecords()) {
                ImportTaskDto item = new ImportTaskDto();
                CopyUtils.copyProperties(entity, item);
                item.setSuccessNum(entity.getSuccessful());
                item.setCreateUser(userName);
                item.setUrl(OssUtils.getUrl(item.getResultFilePath(), 1));
                if ("2".equalsIgnoreCase(item.getStatus()) || "3".equalsIgnoreCase(item.getStatus())) {
                    item.setFailNum(item.getTotal() - item.getSuccessNum());
                } else {
                    item.setFailNum(0);
                }

                list.add(item);
            }
        }

        resultImportTaskDto.setCurrent(result.getCurrent());
        resultImportTaskDto.setSize(result.getSize());
        resultImportTaskDto.setTotal(result.getTotal());
        resultImportTaskDto.setPages(result.getPages());
        resultImportTaskDto.setRecords(list);


        return resultImportTaskDto;
    }


    /**
     * 异步导入
     *
     * @param inputStream
     * @param excelImportConsumer
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     */
    private <T extends ExcelRow> int importExcelAsync(InputStream inputStream,
                                                      IExcelImportConsumer<T> excelImportConsumer, ImportTaskEntity importTask, Map<String, Object> paramMap,
                                                      File resultFile) throws ClassNotFoundException, InstantiationException, IllegalAccessException, IOException {
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (EncryptedDocumentException e1) {
            throw new BusinessException("文件格式错误");
        }

        try {
            return parseExcelAsync(workbook, excelImportConsumer, importTask, paramMap);
        } finally {
            FileOutputStream fos = new FileOutputStream(resultFile);
            workbook.write(fos);
            fos.flush();
            fos.close();
            workbook.close();
        }

    }


    /**
     * 解析文件执行异步导入
     *
     * @param workbook
     * @param excelImportConsumer
     * @return 成功条数
     * @throws IOException
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    private <T extends ExcelRow> int parseExcelAsync(Workbook workbook,
                                                     IExcelImportConsumer<T> excelImportConsumer, ImportTaskEntity importTask, Map<String, Object> paramMap)
            throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException {

        String typeClassStr = String.valueOf(paramMap.get("typeClass"));
        int successful = 0;

        Class typeClass = Class.forName(typeClassStr);

        int sheetIndex = 0;

        Sheet sheet = workbook.getSheetAt(sheetIndex);

        Map<String, Integer> titleMap = getTitleMap(sheet, typeClass);
        checkTemplate(titleMap, typeClass);

        int lastCellNum = 0;
        if (titleMap != null) {
            for (Map.Entry<String, Integer> entry : titleMap.entrySet()) {
                if (entry.getValue() != null) {
                    if (entry.getValue().intValue() > lastCellNum) {
                        lastCellNum = entry.getValue().intValue();
                    }
                }
            }
        }

        Map<ExcelCell, Field> propertyMap = getFieldMap(typeClass);
        Map<ExcelCell, Map<String, String>> propertyFormatMap = getFieldFormatMap(typeClass);

        List<T> successList = new ArrayList<>();
        Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
        int lastRowNum = sheet.getLastRowNum();
        int beginIndex = excel.firstDataRow();
        if (importTask.getLastRowIndex() != null && importTask.getLastRowIndex() > 0) {
            beginIndex = importTask.getLastRowIndex();
        }
        for (int i = beginIndex; i <= lastRowNum; i++) {
            Row row = sheet.getRow(i);
            T t = (T) parseRow(row, typeClass, propertyMap, propertyFormatMap, titleMap);
            try {
                parseRowOfEnum(t, propertyMap);
            } catch (Exception e) {
                t.setErrorMessage(e.getMessage());
            }
            if (t.getErrorMessage() != null) {
                Cell cell = row.createCell(lastCellNum + 1, CellType.STRING);
                cell.setCellValue(t.getErrorMessage());
            } else {
                successList.add(t);
            }

            if ((!successList.isEmpty()) && successList.size() % 100 == 0) {
                int count = excelImportConsumer.consume(successList, importTask, paramMap);
                successful += count;
                writeErrorMessage(sheet, successList, titleMap, workbook);
                successList.clear();
            }
        }

        if (!successList.isEmpty()) {
            int count = excelImportConsumer.consume(successList, importTask, paramMap);
            successful += count;
            writeErrorMessage(sheet, successList, titleMap, workbook);
        }

        return successful;

    }


    private <T extends ExcelRow> Map<String, Integer> getTitleMap(Sheet sheet, Class<T> typeClass)
            throws ClassNotFoundException {
        Map<String, Integer> titleMap = new HashMap<String, Integer>();
        Excel excel = Class.forName(typeClass.getName()).getAnnotation(Excel.class);
        if (excel != null && excel.titleRow() >= 0) {
            Row row = sheet.getRow(excel.titleRow());
            if (row == null) {
                return titleMap;
            }
            int lastCellNum = row.getLastCellNum();
            for (int i = 0; i < lastCellNum; i++) {
                Cell cell = row.getCell(i);
                // String sCellValue = formatCell(cell);
                if (cell == null || StringUtils.isBlank(cell.getStringCellValue())) {
                    throw new BusinessException("标题为空");
                }
                String cellValue = cell.getStringCellValue().trim();
                if (titleMap.containsKey(cellValue)) {
                    throw new BusinessException("列名 " + cellValue + "重复");
                }
                titleMap.put(cellValue, i);
            }
        }

        return titleMap;
    }


    private <T extends ExcelRow> void checkTemplate(Map<String, Integer> titleMap, Class<T> typeClass) {
        List<Field> list = Arrays.asList(getAllFields(typeClass));

        List<String> allTitle = new ArrayList<String>();
        for (int i = 0; i < list.size(); i++) {
            Field field = list.get(i);
            if (field.isAnnotationPresent(ExcelCell.class)) {// 是否使用ExcelCell注解
                for (Annotation anno : field.getDeclaredAnnotations()) {// 获得所有的注解
                    if (anno.annotationType().equals(ExcelCell.class)) {// 找到自己的注解
                        String title = ((ExcelCell) anno).title();
                        if (StringUtils.isNotBlank(title)) {
                            allTitle.add(title);
                            if (titleMap.get(title) == null) {
                                throw new BusinessException("模板错误");
                            }
                        }

                    }
                }
            }
        }
    }

    /**
     * 获取对象所有的属性
     *
     * @param clazz
     * @return
     */
    private Field[] getAllFields(Class clazz) {
        List<Field> fieldList = new ArrayList<>();
        while (clazz != null) {
            fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
            clazz = clazz.getSuperclass();
        }
        Field[] fields = new Field[fieldList.size()];
        fieldList.toArray(fields);
        return fields;
    }


    public static <T extends ExcelRow> T parseRow(Row row, Class<T> typeClass, Map<ExcelCell, Field> propertyMap,
                                                  Map<ExcelCell, Map<String, String>> propertyFormatMap, Map<String, Integer> titleMap)
            throws InstantiationException, IllegalAccessException {
        T t = typeClass.newInstance();
        t.setRowNo(row.getRowNum() + 1);

        try {
            for (Map.Entry<ExcelCell, Field> entry : propertyMap.entrySet()) {
                ExcelCell excelCell = entry.getKey();
                Field field = entry.getValue();

                int cellNum;

                if (!"".equals(excelCell.title())) {
                    Integer index = titleMap.get(excelCell.title());
                    if (index == null) {
                        throw new BusinessException("title: " + excelCell.title() + " does not exist");
                    }
                    cellNum = index;
                } else if (excelCell.index() >= 0) {
                    cellNum = excelCell.index();
                } else {
                    throw new BusinessException("property: " + field.getName() + " not config title or index");
                }

                Cell cell = row.getCell(cellNum);

                try {
                    if (cell != null) {
                        try {
                            setValue(cell, entry, t, propertyFormatMap);
                        } catch (BusinessException e) {
                            throw new BusinessException("第" + (cellNum + 1) + "列" + excelCell.title() + e.getMessage());
                        } catch (Exception e) {
                            throw new BusinessException("第" + (cellNum + 1) + "列" + excelCell.title() + "解析失败");
                        }
                    } else {
                        if (excelCell.require()) {
                            throw new BusinessException("第" + (cellNum + 1) + "列" + excelCell.title() + "不能为空");
                        }
                    }
                } catch (BusinessException e) {
                    if (t.getErrorMessage() == null) {
                        t.setErrorMessage(e.getMessage());
                    } else {
                        t.setErrorMessage(t.getErrorMessage() + " | " + e.getMessage());
                    }
                }
            }

            /*
             * if (dataList.contains(t)) { Integer rowNo = dataList.stream().filter(e ->
             * e.equals(t)).findFirst().get().getRowNo(); throw new BusinessException("第" +
             * (i + 1) + "行和第" + rowNo + "行数据重复"); }
             */
        } catch (BusinessException e) {
            t.setErrorMessage(e.getMessage());
        }
        return t;
    }

    public static void writeErrorMessage(Sheet sheet, List<? extends ExcelRow> dataList, Map<String, Integer> titleMap,
                                         Workbook workbook) {

        int lastCellNum = 0;
        if (titleMap != null) {
            for (Map.Entry<String, Integer> entry : titleMap.entrySet()) {
                if (entry.getValue() != null) {
                    if (entry.getValue().intValue() > lastCellNum) {
                        lastCellNum = entry.getValue().intValue();
                    }
                }
            }
        }

        CellStyle statusStyle = workbook.createCellStyle();
        statusStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        statusStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        CellStyle errorStyle = workbook.createCellStyle();
        errorStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        errorStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        for (ExcelRow excelRow : dataList) {
            if (excelRow.getErrorMessage() != null) {
                Row row = sheet.getRow(excelRow.getRowNo() - 1);
                Cell cellStatus = row.createCell(lastCellNum + 1, CellType.STRING);
                Cell cellError = row.createCell(lastCellNum + 2, CellType.STRING);
                cellStatus.setCellValue("失败");
                cellError.setCellValue(excelRow.getErrorMessage());
                cellStatus.setCellStyle(errorStyle);
            } else {
                Row row = sheet.getRow(excelRow.getRowNo() - 1);
                Cell cellStatus = row.createCell(lastCellNum + 1, CellType.STRING);
                cellStatus.setCellValue("成功");
                cellStatus.setCellStyle(statusStyle);
            }
        }
    }


    private static <T> T setValue(Cell cell, Map.Entry<ExcelCell, Field> entry, T t,
                                  Map<ExcelCell, Map<String, String>> propertyFormatMap) {
        ExcelCell excelCell = entry.getKey();
        Field field = entry.getValue();
        Object value = null;

        Map<String, String> propertyFormatEntry = null;
        propertyFormatEntry = propertyFormatMap.getOrDefault(excelCell, Collections.emptyMap());

        if (cell.getCellType().equals(CellType.STRING) || (cell.getCellType().equals(CellType.FORMULA)
                && cell.getCachedFormulaResultType().equals(CellType.STRING))) {
            String cellValue = cell.getStringCellValue();
            if (excelCell.format().length > 0) {
                String formatValue = propertyFormatEntry.get("_value_" + cellValue);
                if (formatValue != null) {
                    cellValue = formatValue;
                } else if (excelCell.require()) {
                    throw new BusinessException("解析失败");
                }
            } else if (excelCell.matches().length > 0) {
                boolean flag = false;
                for (String str : excelCell.matches()) {
                    if (str.equals(cellValue)) {
                        flag = true;
                        break;
                    }
                }
                if (!flag) {
                    throw new BusinessException("解析失败");
                }
            }

            if (field.getType().equals(String.class)) {
                value = cellValue;
            } else if (field.getType().equals(int.class) || field.getType().equals(Integer.class)) {
                value = Integer.parseInt(cellValue);
            } else if (field.getType().equals(float.class) || field.getType().equals(Float.class)) {
                value = Float.parseFloat(cellValue);
            } else if (field.getType().equals(double.class) || field.getType().equals(Double.class)) {
                value = Double.parseDouble(cellValue);
            } else if (field.getType().equals(long.class) || field.getType().equals(Long.class)) {
                value = Long.parseLong(cellValue);
            } else if (field.getType().equals(BigDecimal.class)) {
                value = new BigDecimal(String.valueOf(cellValue));
            } else if (field.getType().equals(Date.class)) {
                if (excelCell.dateFormats() == null || excelCell.dateFormats().length == 0) {
                    throw new BusinessException("解析失败");
                } else {
                    try {
                        value = DateUtils.parse(cellValue, excelCell.dateFormats());
                    } catch (ParseException e) {
                        throw new BusinessException("解析失败");
                    }
                }
            } else {
                throw new BusinessException("解析失败");
            }
        } else if (cell.getCellType().equals(CellType.NUMERIC) || (cell.getCellType().equals(CellType.FORMULA)
                && cell.getCachedFormulaResultType().equals(CellType.NUMERIC))) {
            boolean dateFlag =
                    DateUtil.isADateFormat(cell.getCellStyle().getDataFormat(), cell.getCellStyle().getDataFormatString());
            if (dateFlag) {
                Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                if (field.getType().equals(String.class)) {
                    value = DateUtils.FORMAT_DATETIME1.format(date);
                } else if (field.getType().equals(Date.class)) {
                    value = date;
                } else if (field.getType().equals(long.class) || field.getType().equals(Long.class)) {
                    value = date.getTime();
                } else {
                    throw new BusinessException("解析失败");
                }
            } else {
                if (field.getType().equals(String.class)) {
                    cell.setCellType(CellType.STRING);
                    value = cell.getStringCellValue();
                    if (excelCell.format().length > 0) {
                        String formatValue = propertyFormatEntry.get("_value_" + value);
                        if (formatValue != null) {
                            value = formatValue;
                        } else if (excelCell.require()) {
                            throw new BusinessException("解析失败");
                        }
                    }
                } else if (field.getType().equals(int.class) || field.getType().equals(Integer.class)) {
                    value = (int) cell.getNumericCellValue();
                    String formatValue = propertyFormatEntry.get(value);
                    if (formatValue != null) {
                        value = formatValue;
                    }
                } else if (field.getType().equals(float.class) || field.getType().equals(Float.class)) {
                    value = (float) cell.getNumericCellValue();
                } else if (field.getType().equals(double.class) || field.getType().equals(Double.class)) {
                    value = cell.getNumericCellValue();
                } else if (field.getType().equals(long.class) || field.getType().equals(Long.class)) {
                    value = (long) cell.getNumericCellValue();
                } else if (field.getType().equals(BigDecimal.class)) {
                    value = BigDecimal.valueOf(cell.getNumericCellValue());
                } else {
                    throw new BusinessException("解析失败");
                }
            }
        } else if (cell.getCellType().equals(CellType.BOOLEAN) || (cell.getCellType().equals(CellType.FORMULA)
                && cell.getCachedFormulaResultType().equals(CellType.BOOLEAN))) {
            boolean cellValue = cell.getBooleanCellValue();
            if (field.getType().equals(String.class)) {
                value = Boolean.toString(cellValue);
                if (excelCell.format().length > 0) {
                    String formatValue = propertyFormatEntry.get("_value_" + value);
                    if (formatValue != null) {
                        value = formatValue;
                    } else if (excelCell.require()) {
                        throw new BusinessException("解析失败");
                    }
                }
            } else if (field.getType().equals(boolean.class) || field.getType().equals(Boolean.class)) {
                value = cellValue;
            } else {
                throw new BusinessException("解析失败");
            }
        } else if (cell.getCellType().equals(CellType.BLANK)) {// 空的时候不需要转换
            // if (excelCell.format().length > 0 || excelCell.matches().length > 0) {
            // throw new BusinessException("解析失败");
            // }
        } else {
            throw new BusinessException("解析失败");
        }

        // 去掉首尾空格
        if (value instanceof String) {
            value = value.toString().trim();
        }

        if (excelCell.require()
                && (value == null || (value instanceof String && !StringUtils.isNotBlank(value.toString())))) {
            throw new BusinessException("不能为空");
        }

        ReflectionUtils.setField(field, t, value);

        return t;
    }

    private <T extends ExcelRow> Map<ExcelCell, Field> getFieldMap(Class<T> typeClass)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException {
        Map<ExcelCell, Field> propertyMap = typeClassFieldCache.get(typeClass.getName());
        if (propertyMap == null || propertyMap.isEmpty()) {
            synchronized (locker) {
                propertyMap = typeClassFieldCache.get(typeClass.getName());
                if (propertyMap == null || propertyMap.isEmpty()) {
                    propertyMap = new HashMap<>();
                    typeClassFieldCache.put(typeClass.getName(), propertyMap);

                    Map<ExcelCell, Map<String, String>> propertyFormatMap = new HashMap<>();
                    typeClassFieldFormatCache.put(typeClass.getName(), propertyFormatMap);
                    //
                    setPropertyMap(typeClass, propertyMap, propertyFormatMap);
                    // Field[] fields = getAllFields(typeClass);
                    // for (Field field : fields) {
                    // ExcelCell excelCell = field.getDeclaredAnnotation(ExcelCell.class);
                    // if (excelCell != null) {
                    // field.setAccessible(true);
                    // propertyMap.put(excelCell, field);
                    // if (excelCell.format() != null && excelCell.format().length > 0) {
                    // Map<String, String> map = new HashMap<>();
                    // propertyFormatMap.put(excelCell, map);
                    // for (String format : excelCell.format()) {
                    // String[] dict = format.split(":");
                    // map.put("_value_" + dict[1], dict[0]);
                    // map.put("_key_" + dict[0], dict[1]);
                    // }
                    // }
                    // }
                    // }
                }
            }
        }
        return propertyMap;
    }


    /**
     * @param typeClass
     * @param propertyMap
     * @param propertyFormatMap
     */
    private void setPropertyMap(Class typeClass, Map<ExcelCell, Field> propertyMap,
                                Map<ExcelCell, Map<String, String>> propertyFormatMap) {
        if (typeClass == null) {
            return;
        }
        Field[] fields = getAllFields(typeClass);
        for (Field field : fields) {
            ExcelCell excelCell = field.getDeclaredAnnotation(ExcelCell.class);
            if (excelCell != null) {
                field.setAccessible(true);
                propertyMap.put(excelCell, field);
                // 格式化
                if (excelCell.format().length > 0) {
                    Map<String, String> map = new HashMap<>();
                    propertyFormatMap.put(excelCell, map);
                    for (String format : excelCell.format()) {
                        String[] dict = format.split(":");
                        map.put("_value_" + dict[1], dict[0]);
                        map.put("_key_" + dict[0], dict[1]);
                    }
                }
                if (excelCell.isParent()) {
                    Class<?> type = field.getType();
                    setPropertyMap(type, propertyMap, propertyFormatMap);
                }
            }
        }
    }


    private <T extends ExcelRow> Map<ExcelCell, Map<String, String>> getFieldFormatMap(Class<T> typeClass)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException {
        Map<ExcelCell, Map<String, String>> propertyFormatMap = typeClassFieldFormatCache.get(typeClass.getName());
        if (propertyFormatMap == null) {
            getFieldMap(typeClass);
            propertyFormatMap = typeClassFieldFormatCache.get(typeClass.getName());
        }
        return propertyFormatMap;
    }


}
